﻿/*
Schema Update script for OliveLite 0.0.2
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO

PRINT N'Dropping DF_Plugin_PluginId...';


GO
ALTER TABLE [Core].[Plugin] DROP CONSTRAINT [DF_Plugin_PluginId];


GO
PRINT N'Dropping DF_Plugin_IsDroppedYN...';


GO
ALTER TABLE [Core].[Plugin] DROP CONSTRAINT [DF_Plugin_IsDroppedYN];


GO
PRINT N'Dropping FK_Permission_Plugin...';


GO
ALTER TABLE [Core].[Permission] DROP CONSTRAINT [FK_Permission_Plugin];


GO
PRINT N'Dropping FK_Container_Plugin...';


GO
ALTER TABLE [Core].[Container] DROP CONSTRAINT [FK_Container_Plugin];


GO
PRINT N'Dropping FK_BusinessUnitDomain_Domain...';


GO
ALTER TABLE [Core].[BUDomain] DROP CONSTRAINT [FK_BusinessUnitDomain_Domain];


GO
PRINT N'Dropping FK_BusinessUnitDomain_BusinessUnit...';


GO
ALTER TABLE [Core].[BUDomain] DROP CONSTRAINT [FK_BusinessUnitDomain_BusinessUnit];


GO
PRINT N'Dropping [Core].[BUDomain]...';


GO
DROP TABLE [Core].[BUDomain];


GO
PRINT N'Altering [Core].[BUAddress]...';


GO
ALTER TABLE [Core].[BUAddress]
    ADD [IsPublicYN] NCHAR (1) CONSTRAINT [DF_BUAddress_IsPublicYN] DEFAULT (N'N') NOT NULL;


GO
PRINT N'Starting rebuilding table [Core].[Plugin]...';


GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRANSACTION;

CREATE TABLE [Core].[tmp_ms_xx_Plugin] (
    [PluginId]          UNIQUEIDENTIFIER CONSTRAINT [DF_Plugin_PluginId] DEFAULT (newid()) ROWGUIDCOL NOT NULL,
    [Name]              NVARCHAR (127)   NOT NULL,
    [Version]           NVARCHAR (127)   NOT NULL,
    [DBVersionRequired] NVARCHAR (127)   NOT NULL,
    [Area]              NVARCHAR (127)   NULL,
    [Controller]        NVARCHAR (127)   NULL,
    [Action]            NVARCHAR (127)   NULL,
    [UserPermission]    NVARCHAR (127)   NULL,
    [AdminController]   NVARCHAR (127)   NULL,
    [AdminAction]       NVARCHAR (127)   NULL,
    [AdminPermission]   NVARCHAR (127)   NULL,
    [IsIndexedYN]       NCHAR (1)        CONSTRAINT [DF_Plugin_IsIndexedYN] DEFAULT (N'N') NOT NULL,
    [IsDroppedYN]       NCHAR (1)        CONSTRAINT [DF_Plugin_IsDroppedYN] DEFAULT (N'N') NOT NULL
);

ALTER TABLE [Core].[tmp_ms_xx_Plugin]
    ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_Plugin] PRIMARY KEY CLUSTERED ([PluginId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

IF EXISTS (SELECT TOP 1 1
           FROM   [Core].[Plugin])
    BEGIN
        INSERT INTO [Core].[tmp_ms_xx_Plugin] ([PluginId], [Name], [Version], [DBVersionRequired], [Area], [Controller], [Action], [UserPermission], [AdminController], [AdminAction], [AdminPermission], [IsDroppedYN])
        SELECT   [PluginId],
                 [Name],
                 [Version],
                 [DBVersionRequired],
                 [Area],
                 [Controller],
                 [Action],
                 [UserPermission],
                 [AdminController],
                 [AdminAction],
                 [AdminPermission],
                 [IsDroppedYN]
        FROM     [Core].[Plugin]
        ORDER BY [PluginId] ASC;
    END

DROP TABLE [Core].[Plugin];

EXECUTE sp_rename N'[Core].[tmp_ms_xx_Plugin]', N'Plugin';

EXECUTE sp_rename N'[Core].[tmp_ms_xx_clusteredindex_PK_Plugin]', N'PK_Plugin', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Creating [Core].[BusinessUnitPlugins]...';


GO
CREATE TABLE [Core].[BusinessUnitPlugins] (
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL,
    [PluginId]       UNIQUEIDENTIFIER NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL
);


GO
PRINT N'Creating PK_BusinessUnitPlugins...';


GO
ALTER TABLE [Core].[BusinessUnitPlugins]
    ADD CONSTRAINT [PK_BusinessUnitPlugins] PRIMARY KEY CLUSTERED ([BusinessUnitId] ASC, [PluginId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [Core].[DebugInfo]...';


GO
CREATE TABLE [Core].[DebugInfo] (
    [DTStamp]  DATETIME       NOT NULL,
    [Sequence] INT            NOT NULL,
    [InfoText] NVARCHAR (MAX) NOT NULL
);


GO
PRINT N'Creating DF_BusinessUnitPlugins_IsDroppedYN...';


GO
ALTER TABLE [Core].[BusinessUnitPlugins]
    ADD CONSTRAINT [DF_BusinessUnitPlugins_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
PRINT N'Creating DF_DebugInfo_DTStamp...';


GO
ALTER TABLE [Core].[DebugInfo]
    ADD CONSTRAINT [DF_DebugInfo_DTStamp] DEFAULT (getdate()) FOR [DTStamp];


GO
PRINT N'Creating FK_Permission_Plugin...';


GO
ALTER TABLE [Core].[Permission] WITH NOCHECK
    ADD CONSTRAINT [FK_Permission_Plugin] FOREIGN KEY ([PluginId]) REFERENCES [Core].[Plugin] ([PluginId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Container_Plugin...';


GO
ALTER TABLE [Core].[Container] WITH NOCHECK
    ADD CONSTRAINT [FK_Container_Plugin] FOREIGN KEY ([PluginId]) REFERENCES [Core].[Plugin] ([PluginId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_BusinessUnitPlugins_BusinessUnit...';


GO
ALTER TABLE [Core].[BusinessUnitPlugins] WITH NOCHECK
    ADD CONSTRAINT [FK_BusinessUnitPlugins_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_BusinessUnitPlugins_Plugin...';


GO
ALTER TABLE [Core].[BusinessUnitPlugins] WITH NOCHECK
    ADD CONSTRAINT [FK_BusinessUnitPlugins_Plugin] FOREIGN KEY ([PluginId]) REFERENCES [Core].[Plugin] ([PluginId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating CK_Plugin_IsDroppedYN...';


GO
ALTER TABLE [Core].[Plugin] WITH NOCHECK
    ADD CONSTRAINT [CK_Plugin_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
PRINT N'Checking existing data against newly created constraints';


GO
ALTER TABLE [Core].[Permission] WITH CHECK CHECK CONSTRAINT [FK_Permission_Plugin];

ALTER TABLE [Core].[Container] WITH CHECK CHECK CONSTRAINT [FK_Container_Plugin];

ALTER TABLE [Core].[BusinessUnitPlugins] WITH CHECK CHECK CONSTRAINT [FK_BusinessUnitPlugins_BusinessUnit];

ALTER TABLE [Core].[BusinessUnitPlugins] WITH CHECK CHECK CONSTRAINT [FK_BusinessUnitPlugins_Plugin];

ALTER TABLE [Core].[Plugin] WITH CHECK CHECK CONSTRAINT [CK_Plugin_IsDroppedYN];


GO
PRINT N'Updating Database Schema version within the database';


GO
UPDATE [Core].[DBSchemaVer] SET Version = '0.0.2' WHERE [Name] = 'Core'

GO
